﻿using _09_1对多.M_1对1;
using _09_1对多.M_多对多;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace _09_1对多
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            // await add_();
            // await 双向导航查询();
            // await 单向导航属性();
            // await 自组织结构();

            //  await OneToOne();

            // await 多对多();

            // await 执行SQL原生语句();

            // await 软删除();

            await 并发令牌();
        }
        static async Task 执行SQL原生语句()
        {
            Console.WriteLine("==================数据插入=====================");
            // 插入
            /*
            FormattableString sql = $" insert into T_Comments (Message,Article1Id)  select Message,Article1Id from T_Comments";
            using (MyDbConetent db = new MyDbConetent())
            {
                var count =await db.Database.ExecuteSqlInterpolatedAsync(sql);
                Console.WriteLine(count);
            }
            */
            Console.WriteLine("==================实体相关数据查询=====================");
            // 查询
            FormattableString sql1 = $"select * from T_Article order by newid()";
            using (MyDbConetent db = new MyDbConetent())
            {
                var res = db.Articles.FromSqlInterpolated(sql1);
                foreach (var item in res)
                {
                    Console.WriteLine(item.Title);
                }
            }
            Console.WriteLine("==================原生查询=====================");
            using (MyDbConetent db = new MyDbConetent())
            {
                string sql = $"select * from (select row_number() over(order by id desc) as rownum,COUNT(*) over(partition by 1) as totalcount,*  from T_Article ) as temp where temp.rownum between (1-1)*2 and (1*2)";
                DbConnection conn = db.Database.GetDbConnection();
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                }
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            long rownum = reader.GetInt64(0);
                            int totacount = reader.GetInt32(1);
                            Console.WriteLine(rownum + "---" + totacount);
                        }
                    }
                }
            }
            Console.WriteLine("==================原生查询（自己写的查询扩展）=====================");
            string cmdText = $"select * from (select row_number() over(order by id desc) as rownum,COUNT(*) over(partition by 1) as totalcount,*  from T_Article ) as temp where temp.rownum between (1-1)*2 and (1*2)";
            using (MyDbConetent db = new MyDbConetent())
            {
                var res = db.ExecuteSqlQuery(cmdText);
                foreach (var item in res)
                {
                    Console.WriteLine(JsonConvert.SerializeObject(item));
                }
                Console.WriteLine(JsonConvert.SerializeObject(res));
            }
            Console.WriteLine("==================原生查询（存储过程）=====================");
            using (MyDbConetent db = new MyDbConetent())
            {
                SqlParameter[] para = new SqlParameter[] {
                    new SqlParameter("@pageIndex",1),
                    new SqlParameter("@pageSize",2)
                };
                var res = db.ExecuteSqlQuery("Pro_GetArticlePage", CommandType.StoredProcedure, para);
                foreach (var item in res)
                {
                    Console.WriteLine(JsonConvert.SerializeObject(item));
                }
                Console.WriteLine(JsonConvert.SerializeObject(res));
            }

            Console.WriteLine("================原生查询（返回datatable）===================");

          



            /*
            Console.WriteLine("==================原生查询（增，删，改）=====================");
            using (MyDbConetent db = new MyDbConetent())
            {
                // 修改
                var id = 4;
                string sql = $"update T_Dogs set Name='啊黄' where id={id}";
                var res = db.ExecuteNonQuery(sql);
                Console.WriteLine(res);

                // 增加
                string sql_1 = "insert into T_Dogs(name) values('来福')";
                var res1 = db.ExecuteNonQuery(sql_1);
                Console.WriteLine(res1);

                // 增加 并获取最新的Id
                string sql_2 = "insert into T_Dogs(name) values('来福1') select @@IDENTITY as id";
                var res2 = db.ExecuteSqlQuery(sql_2);
                foreach (var item in res2)
                {
                    IDictionary<string, object> keyValuePairs = item as IDictionary<string,object> ;
                    Console.WriteLine(keyValuePairs["id"]);
                }
                // 删除
                string sql_3 = "delete T_Dogs where id>8";
                var res3 = db.ExecuteNonQuery(sql_3);
                Console.WriteLine(res3);
            */
        }


        #region 1对多
        static async Task 单向导航属性()
        {
            using (MyDbConetent db = new MyDbConetent())
            {
                var l = db.Leaves.FirstOrDefault();
                if (l == null)
                {
                    User u1 = new User { Name = "申总" };
                    //User u2 = new User { Name = "杨总" };
                    Leave leave = new Leave { Remark = "回家处理拆迁事宜", Requester = u1 };
                    db.Leaves.Add(leave);
                    await db.SaveChangesAsync();

                }
            }
        }
        // 1对多
        //  双向导航查询和单向导航查询   
        static async Task 双向导航查询()
        {
            using (MyDbConetent db = new MyDbConetent())
            {
                // 双向导航=======================================================
                // 查询文章
                var res = db.Articles.Single(a => a.Id == 1);
                Console.WriteLine(JsonConvert.SerializeObject(res));
                // 查询文章和文章的相关评论
                var res1 = db.Articles.Include(a => a.comments).Single(a => a.Id == 1);
                Console.WriteLine(res1.Id + "--" + res1.Title);
                foreach (var item in res1.comments)
                {
                    Console.WriteLine(item.Message);
                }
                Console.WriteLine("---------------3-----------------");
                // 查询评论和评论的文章
                var res2 = db.Comments.Include(a => a.Article1).Single(a => a.Id == 2);
                Console.WriteLine(res2.Message + "--" + res2.Id);
                Console.WriteLine(res2.Article1.Id + "--" + res2.Article1.Title);
                Console.WriteLine("---------------4-----------------");
                // 查询自己想要的列
                var res3 = db.Articles.Select(a => new { a.Id, a.Title }).Single(a => a.Id == 1);
                Console.WriteLine(res3);
                Console.WriteLine("---------------5-----------------");
                // 单向导航========================================================
                var res4 = db.Leaves.Include(a => a.Requester).Include(a => a.Approver).Single(a => a.Id == 2);
                Console.WriteLine(JsonConvert.SerializeObject(res4));

                // 复杂查询========================================================

                Console.WriteLine("---------------6-----------------");
                // 写法一 从文章开始写
                // .Any 至少有一条
                var res5 = db.Articles.Where(a => a.comments.Any(c => c.Message.Contains("国货")));
                foreach (var item in res5)
                {
                    Console.WriteLine(item.Title);
                }
                Console.WriteLine("---------------7-----------------");
                // 写法二  从评论开始写
                // 没有Distinct() 会有重复数据。 
                var res6 = db.Comments.Where(a => a.Message.Contains("国货")).Select(a => a.Article1).Distinct();
                foreach (var item in res6)
                {
                    Console.WriteLine(item.Title);
                }




            }
        }
        // 双向导航插入数据
        static async Task add_()
        {
            using (MyDbConetent db = new MyDbConetent())
            {
                Article article = new Article();
                article.Title = "aggie是一个C#程序员";
                article.Content = "afdlsdjfsldjflsdjfliwierwer";


                Comment c1 = new Comment { Message = "太牛了" };
                Comment c2 = new Comment { Message = "太牛了2222" };
                article.comments.Add(c1);
                article.comments.Add(c2);

                db.Articles.Add(article);

                await db.SaveChangesAsync();

            }
        }

        static async Task 自组织结构()
        {
            using (MyDbConetent db = new MyDbConetent())
            {

                var resCount = db.OrgUnits.Count();
                if (resCount <= 0)
                {
                    // 数据插入
                    OrgUnit root = new OrgUnit { Name = "全球总部" };

                    OrgUnit yatai = new OrgUnit { Name = "亚太总部" };
                    OrgUnit meizhou = new OrgUnit { Name = "美洲总部" };
                    root.Child.Add(yatai);
                    root.Child.Add(meizhou);

                    OrgUnit meiguo = new OrgUnit { Name = "美国" };
                    OrgUnit jianada = new OrgUnit { Name = "加拿大" };
                    meizhou.Child.Add(meiguo);
                    meizhou.Child.Add(jianada);

                    OrgUnit china = new OrgUnit { Name = "中国" };
                    OrgUnit xinJiaPo = new OrgUnit { Name = "新加坡" };
                    yatai.Child.Add(china);
                    yatai.Child.Add(xinJiaPo);

                    db.OrgUnits.Add(root);
                    await db.SaveChangesAsync();
                }
                else
                {
                    // 查询
                    var root = db.OrgUnits.Where(c => c.Parent == null).FirstOrDefault();
                    Console.WriteLine(root.Name);
                    PrintOrgUnit(1, db, root);

                }


            }
        }
        static void PrintOrgUnit(int level, MyDbConetent db, OrgUnit parent)
        {
            var children = db.OrgUnits.Where(o => o.Parent == parent);
            foreach (var child in children)
            {
                Console.WriteLine(new String('\t', level) + child.Name);
                PrintOrgUnit(level + 1, db, child);
            }
        }
        #endregion
        /// <summary>
        /// 1对1
        /// </summary>
        /// <returns></returns>
        static async Task OneToOne()
        {
            // 1对1必须建一个外键，2变随便挑选一个
            using (MyDbConetent db = new MyDbConetent())
            {
                var res = db.Orders.Count();
                Console.WriteLine(res);
                if (res <= 0)
                {
                    Order order = new Order
                    {
                        Name = "春季运动鞋",
                        Address = "猫眼科技"
                    };
                    Delivery de = new Delivery
                    {
                        CompanyName = "安踏",
                        Numbers = "0000022312312131"
                    };
                    order.Delivery = de;

                    db.Orders.Add(order);
                    await db.SaveChangesAsync();
                }
                else
                {
                    Console.WriteLine("查询1对1.。。。。。。");
                    var res1 = db.Orders.Include(e => e.Delivery).Select(s => s);
                    foreach (Order order in res1)
                    {
                        Console.WriteLine($"{order.Name},{order.Address},{order.Delivery.Numbers},{order.Delivery.CompanyName},{order.Delivery.OrderId}");
                    }
                }
            }

        }

        static async Task 多对多()
        {

            using (MyDbConetent db = new MyDbConetent())
            {
                var res = db.Students.Count();
                if (res <= 0)
                {
                    Student s1 = new Student { Name = "张三" };
                    Student s2 = new Student { Name = "李四" };
                    Student s3 = new Student { Name = "王五" };

                    Teacher t1 = new Teacher { Name = "Tom" };
                    Teacher t2 = new Teacher { Name = "Jerry" };
                    Teacher t3 = new Teacher { Name = "Zack" };

                    s1.Teachers.Add(t1);
                    s1.Teachers.Add(t2);

                    s2.Teachers.Add(t2);
                    s2.Teachers.Add(t3);

                    s3.Teachers.Add(t1);
                    s3.Teachers.Add(t2);
                    s3.Teachers.Add(t3);

                    db.Students.Add(s1);
                    db.Students.Add(s2);
                    db.Students.Add(s3);

                    await db.SaveChangesAsync();
                }
                else
                {
                    Console.WriteLine("多对多查询.......");
                    var res2 = db.Students.Include(t => t.Teachers);
                    foreach (var s in res2)
                    {
                        Console.WriteLine(s.Name);
                        foreach (var t in s.Teachers)
                        {
                            Console.WriteLine(new string('\t', 1) + t.Name);
                        }

                    }

                }
            }
        }


        // 软删除  使用全局查询删选器
        static async Task 软删除()
        {
            using (MyDbConetent db = new MyDbConetent())
            {
                // 查询
                foreach (var i in db.Articles)
                {
                    Console.WriteLine(i.Id+"--"+i.Title);
                }
                // 修改状态
                //var a= db.Articles.Single(a => a.Id == 3);
                //a.IsDeleted = true;
                //await db.SaveChangesAsync();
                // 全局
                foreach (var i in db.Articles)
                {
                    Console.WriteLine(i.Id + "--" + i.Title);
                }
                // 忽略全局IgnoreQueryFilters()
                foreach (var i in db.Articles.IgnoreQueryFilters())
                {
                    Console.WriteLine(i.Id + "--" + i.Title);
                }
            }
        }

        static async Task 并发令牌()
        {
            Console.WriteLine("请输入你的名字");
            string name = Console.ReadLine();
            using (MyDbConetent db=new MyDbConetent())
            {
                var h = db.Hours.Single(a => a.Id == 1);
                if (!string.IsNullOrEmpty(h.Owen))
                {
                    if (h.Owen == name)
                    {
                        Console.WriteLine("房子已经被你抢了");
                    }
                    else {
                        Console.WriteLine($"房子已经被{h.Owen}占了");
                    }
                }
                h.Owen= name;


                Thread.Sleep(10000);
                try
                {
                    db.SaveChanges();
                    Console.WriteLine("恭喜抢到了");
                }
                catch (DbUpdateConcurrencyException ex)
                {
                    Console.WriteLine("并发冲突");
                    var entry1=ex.Entries.First();
                    var nameValue=entry1.GetDatabaseValues().GetValue<string>("Owen");
                    Console.WriteLine($"被{nameValue}占领了");
                }

               
            }
            Console.ReadLine();
        }



    }
}
